package com.es.hr.shop;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.business.city.CityBean;
import com.es.business.city.CityBiz;
import com.es.business.citys.CitysBean;
import com.es.business.citys.CitysBiz;
import com.es.business.province.ProvinceBean;
import com.es.business.province.ProvinceBiz;
import com.es.hr.emp.EmpInfoBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class ShopBiz {
	public boolean insertShop(ShopBean bean) throws Exception{
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		StringBuffer sb = new StringBuffer();
		StringBuffer sb1 = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.shop.ShopBean").getDeclaredFields();   
		 
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals("shop_id")){

			}else{
				sb.append(fds[i].getName()+",");
				sb1.append("?,");
				query.add((String)fds[i].get(bean));
			} 	  
		}		
		sb.append("del_flag"+",");
		sb1.append("?,");
		query.add("N");
		try {
			String sql = "insert into hrm_shop_info("+ sb.toString().substring(0, sb.toString().length()-1)+") values("+
				sb1.toString().substring(0, sb1.toString().length()-1)+" )";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	@SuppressWarnings("unchecked")
	public boolean importShopExcelList( ArrayList<ArrayList> queryParms) throws Exception{
		Dao dao = new Dao();
		try {
			String sql = "insert into hrm_shop_export(shop_code,shop_name,shop_maichang_code,shop_maichang_value,shop_maichang_type,shop_area_value,shop_province_value,shop_city_value,shop_city_level,shop_citys_value,shop_position_code,shop_mail_code,shop_position_value,shop_jingxiaoshang_code,shop_jingxiaoshang_value,shop_jingxiaoshang_type,shop_level,shop_brand,shop_ctm_name,shop_index,session_id,create_time) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			dao.updateBatch(sql,queryParms);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	@SuppressWarnings("unchecked")
	public ArrayList<HashMap<String, Object>> importShopExcelListDo(String sessionId,String intime) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String, Object>> map = new ArrayList<HashMap<String, Object>>();
		ArrayList<String> query = new ArrayList<String>();
		query.add(sessionId);
		query.add(intime);
		try {
			String sql = ("{call export_check_shop(?,?)}");
			ResultSet resultSet = dao.executePro(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
				if(rss.getColumnCount()==1){
					HashMap<String, Object> rowNum = new HashMap<String, Object>();
					rowNum.put("rowNum", (String) resultSet.getObject(rss.getColumnName(1)));
					map.add(rowNum);
					return map;
				}
				HashMap<String, Object> errorlist = new HashMap<String, Object>();
				//shop_code,shop_name,shop_index,error
				for(int i = 0;i<rss.getColumnCount(); i++){ 	
					errorlist.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));
			 }	
				map.add(errorlist);
		  }
		} catch(Exception e){
				System.out.print(e.getMessage());
		   }finally {		   
			   dao.close();
		    }
		    return map;
	}
	
	public boolean modifyShopById(ShopBean bean, String shopId) throws Exception{
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		StringBuffer sb = new StringBuffer();
		StringBuffer sb1 = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.shop.ShopBean").getDeclaredFields();   
		String sql = "update hrm_shop_info set ";
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals("shop_id")){

			}else{
				sb.append(fds[i].getName()+"= ?,");
				query.add((String)fds[i].get(bean));
			} 	  
		}
		query.add(shopId);
		try {
			 sql = sql + sb.toString().substring(0,sb.toString().length() - 1) + " where shop_id =?";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	
	public PageBean searchShop(String area_code,String province_code, String city_code, String queryShopCode, String queryShopName, int curpage,String rtmAreaCode)
	throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = " where del_flag = ? ";
		String[] sqlp = {" shop_area_code = ? ", " shop_province_code = ? ", " shop_city_code =? "," shop_code like '%'+UPPER(?)+'%' "," shop_name like '%'+UPPER(?)+'%' "};
		String and = " and ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add("N");
		sqlParam2.add(area_code);
		sqlParam2.add(province_code);
		sqlParam2.add(city_code);
		sqlParam2.add(queryShopCode);
		sqlParam2.add(queryShopName);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}

			}
		if(!rtmAreaCode.equals("")){
			sql += and + " shop_area_code = ?";
			sqlParam.add(rtmAreaCode);
		}
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.shop.ShopBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append(fds[i].getName()+",");
		}		
		
		sql = " select " + sb.toString().substring(0, sb.toString().length() - 1)+ " from hrm_shop_info " + sql;
		
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			for(int i = 0; i < pb.getMap().size(); ++i){
				HashMap<String, Object> map = pb.getMap().get(i);
				String cCode = map.get("shop_city_code").toString();
				if(cCode != null && cCode.length() > 0){
					CityBean crs = new CityBiz().getCityByCode(cCode);
					map.put("shop_city_level", crs.getCityLevel());
				}else{
					map.put("shop_city_level", "");
				}
			}
			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public PageBean searchCTMShop(String area_code,String province_code, String city_code, String queryShopCode, String queryShopName, String ctmCode, int curpage)
	throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = " where del_flag = ? ";
		String[] sqlp = {" shop_area_code = ? ", " shop_province_code = ? ", " shop_city_code=? "," shop_code like '%'+UPPER(?)+'%' "," shop_name like '%'+UPPER(?)+'%' ", "shop_ctm_code =?"};
		String and = " and ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add("N");
		sqlParam2.add(area_code);
		sqlParam2.add(province_code);
		sqlParam2.add(city_code);
		sqlParam2.add(queryShopCode);
		sqlParam2.add(queryShopName);
		sqlParam2.add(ctmCode);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}

			}
		
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.shop.ShopBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append(fds[i].getName()+",");
		}		
		
		sql = " select " + sb.toString().substring(0, sb.toString().length() - 1)+ " from hrm_shop_info " + sql;
		
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			for(int i = 0; i < pb.getMap().size(); ++i){
				HashMap<String, Object> map = pb.getMap().get(i);
				
				String cCode = map.get("shop_city_code").toString();
				if(cCode != null && cCode.length() > 0){
					CityBean crs = new CityBiz().getCityByCode(cCode);
					map.put("shop_city_level", crs.getCityLevel());
				}else{
					map.put("shop_city_level", "");
				}
			}
			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public PageBean searchNotCTMShop(String area_code,String province_code, String city_code, String queryShopCode, String queryShopName, String ctmCode, int curpage,String hasRTM)
	throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = " where del_flag = ? ";
		String[] sqlp = {" shop_area_code = ? ", " shop_province_code = ? ", " shop_city_code=? "," shop_code like '%'+UPPER(?)+'%' "," shop_name like '%'+UPPER(?)+'%' ", "  (shop_ctm_code != ? or shop_ctm_code is null) "};
		String and = " and ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam.add("N");
		sqlParam2.add(area_code);
		sqlParam2.add(province_code);
		sqlParam2.add(city_code);
		sqlParam2.add(queryShopCode);
		sqlParam2.add(queryShopName);
		sqlParam2.add(ctmCode);
		
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
			}

		}
		
		if(hasRTM!=null){
			String sqlCtm="";
			if("N".equals(hasRTM))
				sqlCtm = " and shop_ctm_code is null ";
			if("Y".equals(hasRTM))
				sqlCtm = " and shop_ctm_code is not null ";
			if(!"".equals(sqlCtm))		
				sql += sqlCtm;				
			
			
		}
			
		StringBuffer sb = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.shop.ShopBean").getDeclaredFields();
		
		for(int i=0;i<fds.length;i++){   
				sb.append(fds[i].getName()+",");
		}		
		
		sql = " select " + sb.toString().substring(0, sb.toString().length() - 1)+ " from hrm_shop_info " + sql;
		
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			for(int i = 0; i < pb.getMap().size(); ++i){
				HashMap<String, Object> map = pb.getMap().get(i);
				
				String cCode = map.get("shop_city_code").toString();
				if(cCode != null && cCode.length() > 0){
					CityBean crs = new CityBiz().getCityByCode(cCode);
					map.put("shop_city_level", crs.getCityLevel());
				}else{
					map.put("shop_city_level", "");
				}
			}
			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public ShopBean getShopById(String id) throws Exception{
		ShopBean bean = new ShopBean();
		if(id == null) return bean;
		
		ArrayList<String> query = new ArrayList<String>();
		query.add(id);
		Dao dao = new Dao();
		String sql = "select top 1 *  from  hrm_shop_info  where shop_id = ? ";
		try {
			ResultSet resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData(); 
			if(resultSet.next()){
				  for(int i = 0;i<rss.getColumnCount(); i++){ 
					  
					  if(rss.getColumnName(i+1).equals("shop_id"))
						  bean.setShop_id(new Long(resultSet.getLong("shop_id")).toString());
					  else
						  ShopInfoBeanSet(bean,rss.getColumnName(i+1),(String) resultSet.getObject(rss.getColumnName(i+1)));
				 }	
			  }
		} finally{
			dao.close();
		}
		return bean;
	}
	
	private void ShopInfoBeanSet(ShopBean bean,String field,String value) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException{
		Field[] fds = Class.forName("com.es.hr.shop.ShopBean").getDeclaredFields();   
		
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals(field)){
				fds[i].set(bean, value);
				return;
			}
		}
	}
	
	public boolean deleteShopById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_shop_info set del_flag = 'Y' where shop_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean updateShopCTMById(String ctmCode, String ctmName, String shopId) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_shop_info  set shop_ctm_code = ? , shop_ctm_name = ?"
		+ " where shop_id = ? and del_flag = 'N'";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(ctmCode);
		sqlParam.add(ctmName);
		sqlParam.add(shopId);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean deleteShopCTMById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_shop_info set shop_ctm_code = '', shop_ctm_name='' where shop_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<HashMap<String,String>> exceptExcelShop(ArrayList<String> param)
	throws Exception {
	Dao dao = new Dao();
	ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
	ArrayList<String> query = new ArrayList<String>();

	try {
		String sql = "select shop.*,"
			+"(select top 1 common_value from hrm_common where common_code = shop.shop_jingxiaoshang_type and common_type='jingxiaoshangtype') shop_jingxiaoshang_value"
			+" from hrm_shop_info shop where shop.del_flag='N' ";
		String[] sqlArrary = new String[] { "and shop.shop_area_code=? ",
				"and shop.shop_province_code=? ", "and shop.shop_city_code=? ",
				"and UPPER(shop.shop_code) like '%'+UPPER(?)+'%'",
				"and UPPER(shop.shop_name) like '%'+UPPER(?)+'%'"
				};
		
		for (int i = 0; i < param.size(); ++i) {
			if (!param.get(i).equals("")) {
				sql += sqlArrary[i];
				query.add(param.get(i).trim());
			}
		}
		ResultSet resultSet = null;
		if(query.size()==0)
			resultSet = dao.Select(sql);
		else
			resultSet = dao.Select(sql,query);
		ResultSetMetaData rss = resultSet.getMetaData();
		
		while(resultSet.next()){
				HashMap<String,String> map = new HashMap<String,String>();
				for(int i = 0;i<rss.getColumnCount(); i++){ 	
					if(!rss.getColumnName(i+1).equals("shop_id"))
						map.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));
					if(rss.getColumnName(i+1).equals("shop_city_code")){
						map.put("shop_city_level", new CityBiz().getCityByCode((String) resultSet.getObject(rss.getColumnName(i+1))).getCityLevel());
					}
				}	
			  list.add(map);
		  }
	} finally {
		dao.close();
	}
	return list;
	}
}
